package com.fjh.springboot_01.mapper;

import com.fjh.springboot_01.pojo.lesson.Lesson;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

@Mapper
public interface LessonMapper {
    //新增
    @Insert("insert into lesson(title,content,cover_img,video_url,state,category_id,create_user,create_time,update_time) " +
            "values(#{title},#{content},#{coverImg},#{videoUrl},#{state},#{categoryId},#{createUser},#{createTime},#{updateTime})")
    void add(Lesson lesson);

    // 条件分页查询，支持模糊查询
    List<Lesson> list(
            @Param("userId") Integer userId,
            @Param("categoryId") Integer categoryId,
            @Param("keyword") String keyword,
            @Param("start") Integer start,
            @Param("pageSize") Integer pageSize
    );

    // 统计总记录数
    Integer count(
            @Param("userId") Integer userId,
            @Param("categoryId") Integer categoryId,
            @Param("keyword") String keyword
    );

    // 根据ID删除课程
    @Delete("delete from lesson where id = #{id}")
    void deleteById(Integer id);

    // 更新课程信息
    @Update("update lesson set title=#{title}, content=#{content}, cover_img=#{coverImg}, " +
            "video_url=#{videoUrl}, state=#{state}, category_id=#{categoryId}, " +
            "update_time=#{updateTime} where id=#{id}")
    void update(Lesson lesson);

    // 获取课程创建者ID
    @Select("SELECT create_user FROM lesson WHERE id = #{id}")
    Integer getLessonCreator(@Param("id") Integer id);
    
    // 根据ID列表获取课程
    List<Lesson> findByIds(@Param("ids") List<Integer> ids);

    /**
     * 获取热门课程
     * @param limit 限制数量
     * @return 热门课程列表
     */
    @Select("SELECT " +
            "l.*, " +
            "u.username as createUserName, " +
            "c.category_name as categoryName, " +
            "(SELECT COUNT(*) FROM lesson_like WHERE lesson_id = l.id) as likeCount, " +
            "(SELECT COUNT(*) FROM lesson_favorite WHERE lesson_id = l.id) as favoriteCount " +
            "FROM lesson l " +
            "LEFT JOIN user u ON l.create_user = u.id " +
            "LEFT JOIN category c ON l.category_id = c.id " +
            "WHERE l.state = '已发布' " +
            "ORDER BY likeCount + favoriteCount DESC " +
            "LIMIT #{limit}")
    List<Lesson> getPopularLessons(@Param("limit") int limit);

    /**
     * 通过ID列表获取课程
     * @param ids 课程ID列表
     * @return 课程列表
     */
    @Select("<script>" +
            "SELECT " +
            "l.*, " +
            "u.username as createUserName, " +
            "c.category_name as categoryName, " +
            "(SELECT COUNT(*) FROM lesson_like WHERE lesson_id = l.id) as likeCount, " +
            "(SELECT COUNT(*) FROM lesson_favorite WHERE lesson_id = l.id) as favoriteCount " +
            "FROM lesson l " +
            "LEFT JOIN user u ON l.create_user = u.id " +
            "LEFT JOIN category c ON l.category_id = c.id " +
            "WHERE l.id IN " +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            " AND l.state = '已发布'" +
            "</script>")
    List<Lesson> getLessonsByIds(@Param("ids") List<Integer> ids);

    /**
     * 获取所有用户的点赞数据
     * @return 点赞数据列表
     */
    @Select("SELECT user_id as userId, lesson_id as lessonId FROM lesson_like")
    List<Map<String, Object>> getAllLikes();

    /**
     * 获取所有用户的收藏数据
     * @return 收藏数据列表
     */
    @Select("SELECT user_id as userId, lesson_id as lessonId FROM lesson_favorite")
    List<Map<String, Object>> getAllFavorites();

    // 根据ID获取课程
    @Select("SELECT * FROM lesson WHERE id = #{id}")
    Lesson getById(@Param("id") Integer id);
}
